<?php
/**
 * 每日销售量报表
 * (model)
 */

namespace App\Models\SalesReport;

use App\Models\Basics\SysArea;
use App\Models\Basics\SysCountry;
use App\Models\Basics\SysPlat;
use App\Models\Inventory\PublicCn;
use App\Models\Product\ProductClassify;
use App\Models\Product\ProductSku;
use App\Models\SsBasic\AreaSkuEstimate;
use App\Util\User;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class DailyVolume extends Model{

    protected $table = 'sr_daily_volume';

    protected $guarded = [];

    public $timestamps = false;

    public function sku(){
        return $this->belongsTo(ProductSku::class, 'psku_id');
    }

    public function classify(){
        return $this->belongsTo(ProductClassify::class,'classify_id');
    }


    /**
     * 获取往期销量汇总（年）
     * @param $requestParam
     * @param null $area
     * @param int $year
     * @return array
     */
    public static function getPrevVolume($requestParam, $area = null, $year = 1){

        //往期汇总(年)
        $prevData = [];
        for($i = 0; $i < AreaSkuEstimate::ESTIMATE_NUM; $i++){

            $prevYear = date('Y-m-d',strtotime("-$year year")); //年
            $prevMonth = date('Y-m-d',strtotime("midnight first day of +$i month", strtotime($prevYear))); //月
            $monthDate = date('y/m', strtotime($prevMonth)); //月

            $prevYearMonthStartDate = date('Y-m-01', strtotime($prevMonth));
            $prevYearMonthEndDate = date('Y-m-t', strtotime($prevMonth));
            $volume = 0;
            $month = '';
            if ($i < 12) {
                $query = DailyVolume::query()
                    ->selectRaw(DB::raw('sum(IFNULL(volume,0)) as volume'))
                    ->whereBetween('report_date', [$prevYearMonthStartDate, $prevYearMonthEndDate]);
                //平台
                $platId = $requestParam['plat_id'] ?? null;
                if($platId){
                    $query->where(function ($query) use($platId){
                        $query->where('plat_id', $platId);
                    });
                }
                //sku
                $sku = $requestParam['sku8'] ?? null;
                if($sku){
                    $query->whereHas('sku', function ($query) use($sku){
                        $query->where('psku_code', 'like', "%{$sku}%")
                            ->orWhere('psku_cn', 'like', "%{$sku}%");
                    });
                }
                //品类
                $classifyId = $requestParam['sku']['classify_id'] ?? null;
                if($classifyId){
                    $query->where(function ($query) use($classifyId){
                        $query->where('classify_id', $classifyId);
                    });
                }
                //季节
                $season = $requestParam['skuClassify']['season'] ?? null;
                if($season){
                    $query->where(function ($query) use($season){
                        $query->where('season', $season);
                    });
                }
                //站点
                if ($area == 'EU') {
                    $areaId = 4; //EU备货
                    $amazonPlatId = SysPlat::PLAT_AMAZON; //amazon
                    $query->where(function ($query) use($areaId,$amazonPlatId){
                        $query->where('area_id', $areaId);
                        $query->where('plat_id', $amazonPlatId);
                    });
                }elseif ($area == 'JP') {
                    $areaId = 6; //JP备货
                    $query->where(function ($query) use($areaId){
                        $query->where('area_id', $areaId);
                    });
                }else{
                    //搜索、查询条件 (站点)
                    $areaId = $requestParam['area_id'] ?? null;
                    if($areaId){
                        $query->where(function ($query) use($areaId){
                            $query->where('area_id', $areaId);
                        });
                    }
                }
                //销售人员
                $sellerId = $requestParam['seller_id'] ?? null;
                if($sellerId){
                    $query->where(function ($query) use($sellerId){
                        $query->where('seller_id', $sellerId);
                    });
                }
                //skuId
                $skuIds = $requestParam['sku_id'] ?? [];
                if($skuIds){
                    $query->whereIn('psku_id', $skuIds);
                }

                $volume = $query->sum('volume');
                $month = $monthDate;
            }
            $prevData['month'.$i] = $month;
            $prevData['estimate_num'.$i] = $volume;
        }

        return $prevData;
    }

    /**
     * 获取时间段内的每日销量-sku (时间段,日)
     * @param $startTime
     * @param $endTime
     * @param null $platId
     * @param null $pskuId
     * @param null $countryId
     * @return array
     */
    public static function getSkuDaysVolume($startTime, $endTime, $platId = null, $pskuId = null, $countryId = null){
        $query = self::query()->whereBetween('report_date', [$startTime, $endTime]);
        if ($platId) {
            $query->where('plat_id', $platId);
        }
        if ($pskuId) {
            $query->where('psku_id', $pskuId);
        }
        if ($countryId) {
            $query->where('country_id', $countryId);
        }

        return $query->pluck('volume', 'report_date')->toArray();
    }

    /**
     * 当日的销量汇总-sku (日期)
     * @param $reportDate
     * @param null $sku
     * @param null $platId
     * @param null $countryId
     * @param null $areaId
     * @param null $sellerId
     * @param null $classifyId
     * @param null $season
     * @param null $reportStatus
     * @return int|mixed
     */
    public static function getSkuDayVolumeSum($reportDate, $sku = null, $platId = null, $countryId = null, $areaId = null, $sellerId = null, $classifyId = null, $season = null, $reportStatus = null){
        $query = self::query()->where('report_date',$reportDate);
        if ($sku) {
            $query->whereHas('sku', function ($query) use($sku){
                $query->where('psku_code', 'like', "%{$sku}%")
                    ->orWhere('psku_cn', 'like', "%{$sku}%");
            });
        }
        if ($platId) $query->where('plat_id', $platId);
        if ($countryId) $query->where('country_id', $countryId);
        if ($areaId) $query->where('area_id', $areaId);
        if ($sellerId) $query->where('seller_id', $sellerId);
        if ($classifyId) $query->where('classify_id', $classifyId);
        if ($season) $query->where('season', $season);
        if ($reportStatus) {
            $query->whereHas('classify', function ($query) use($reportStatus){
                $query->where('report_status', $reportStatus);
            });
        }

        return $query->sum('volume');
    }

    /**
     * 获取时间段内的销量汇总-sku (时间段)
     * @param $startTime
     * @param $endTime
     * @param null $sku
     * @param null $pskuId
     * @param null $platId
     * @param null $countryId
     * @param null $areaId
     * @param null $sellerId
     * @param null $classifyId
     * @param null $season
     * @param null $reportStatus
     * @return int|mixed
     */
    public static function getSkuDaysVolumeSum($startTime, $endTime, $sku = null, $pskuId = null, $platId = null, $countryId = null, $areaId = null, $sellerId = null, $classifyId = null, $season = null, $reportStatus = null){
        $query = self::query()->whereBetween('report_date', [$startTime, $endTime]);
        if ($sku) {
            $query->whereHas('sku', function ($query) use($sku){
                $query->where('psku_code', 'like', "%{$sku}%")
                    ->orWhere('psku_cn', 'like', "%{$sku}%");
            });
        }
        if ($pskuId) $query->where('psku_id', $pskuId);
        if ($platId) $query->where('plat_id', $platId);
        if ($countryId) $query->where('country_id', $countryId);
        if ($areaId) $query->where('area_id', $areaId);
        if ($sellerId) $query->where('seller_id', $sellerId);
        if ($classifyId) $query->where('classify_id', $classifyId);
        if ($season) $query->where('season', $season);
        if ($reportStatus) {
            $query->whereHas('classify', function ($query) use($reportStatus){
                $query->where('report_status', $reportStatus);
            });
        }

        return $query->sum('volume');
    }

    /**
     * 获取时间段内的每日销量-品类 (时间段)
     * @param $startTime
     * @param $endTime
     * @param null $platId
     * @param null $classifyId
     * @param null $areaId
     * @return array
     */
    public static function getClassifyDaysVolume($startTime, $endTime, $platId = null, $classifyId = null, $areaId = null){
        $query = self::query()->whereBetween('report_date', [$startTime, $endTime]);
        if ($platId) $query->where('plat_id', $platId);
        if ($areaId) $query->where('area_id', $areaId);
        if ($classifyId) $query->where('classify_id', $classifyId);

        return $query->selectRaw(DB::raw('sum(volume) as volume, report_date'))
                     ->groupBy('report_date')
                     ->pluck('volume', 'report_date')
                     ->toArray();
    }

    /**
     * 获取时间段内各站点的每日销量-品类 (时间段)
     * @param $startTime
     * @param $endTime
     * @param null $platId
     * @param null $areaId
     * @param null $classifyId
     * @return array
     */
    public static function getAreaClassifyDaysVolume($startTime, $endTime, $platId = null, $areaId = null, $classifyId = null){
        $query = self::query()->whereBetween('report_date', [$startTime, $endTime]);
        if ($platId) $query->where('plat_id', $platId);
        if ($areaId) $query->where('area_id', $areaId);
        if ($classifyId) $query->where('classify_id', $classifyId);

        return $query->selectRaw(DB::raw('sum(volume) as volume, report_date, plat_id, area_id'))
                     ->groupBy('report_date')
                     ->get()
                     ->map(function ($model){
                        return collect([
                            'volume'   => $model->volume,
                            'report_date' => $model->plat_id.'_'.$model->report_date
                        ]);
                     })
                     ->pluck('volume', 'report_date')
                     ->toArray();
    }

    /**
     * 当日的销量汇总-品类 (日期)
     * @param $reportDate
     * @param null $platId
     * @param null $classifyId
     * @param null $season
     * @param null $reportStatus
     * @param null $areaId
     * @return int|mixed
     */
    public static function getClassifyDayVolumeSum($reportDate, $platId = null, $classifyId = null, $season = null, $reportStatus = null, $areaId = null){
        $query = self::query()->where('report_date',$reportDate);
        if ($platId) $query->where('plat_id', $platId);
        if ($areaId) $query->where('area_id', $areaId);
        if ($classifyId) $query->where('classify_id', $classifyId);
        if ($season) $query->where('season', $season);
        if ($reportStatus) {
            $query->whereHas('classify', function ($query) use($reportStatus){
                $query->where('report_status', $reportStatus);
            });
        }

        return $query->sum('volume');
    }

    /**
     * 获取时间段内的销量汇总-品类 (时间段)
     * @param $startTime
     * @param $endTime
     * @param null $platId
     * @param null $classifyId
     * @param null $season
     * @param null $reportStatus
     * @param null $areaId
     * @param null $countryId
     * @return int|mixed
     */
    public static function getClassifyDaysVolumeSum($startTime, $endTime, $classifyId = null, $platId = null, $season = null, $reportStatus = null, $areaId = null,$countryId = null){
        $query = self::query()->whereBetween('report_date', [$startTime, $endTime]);
        if ($classifyId) $query->where('classify_id', $classifyId);
        if ($platId) $query->where('plat_id', $platId);
        if ($areaId) $query->where('area_id', $areaId);
        if ($countryId) $query->where('country_id', $areaId);
        if ($season) $query->where('season', $season);
        if ($reportStatus) {
            $query->whereHas('classify', function ($query) use($reportStatus){
                $query->where('report_status', $reportStatus);
            });
        }

        return $query->sum('volume');
    }

    /**
     * 获取时间段内各SKU的每日销量-品类 (时间段)
     * @param $startTime
     * @param $endTime
     * @param $platId
     * @param $pskuId
     * @return array
     */
    public static function getSkuClassifyDaysVolume($startTime, $endTime, $platId = null, $pskuId = null){
        $query = self::query()->whereBetween('report_date', [$startTime, $endTime]);
        if ($platId) $query->where('plat_id', $platId);
        if ($pskuId) $query->where('psku_id', $pskuId);

        return $query->selectRaw(DB::raw('sum(volume) as volume, report_date, plat_id, area_id'))
                     ->groupBy('report_date')
                     ->get()
                     ->map(function ($model){
                        return collect([
                            'volume'   => $model->volume ?? 0,
                            'report_date' => $model->plat_id.'_'.$model->report_date
                        ]);
                     })
                     ->pluck('volume', 'report_date')
                     ->toArray();
    }
}
